/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package BD.Modulos;
import BD.Bd;
import BD.ConnBdType;
import Interfaces.BdException;
import Interfaces.ConfigInterface;
import Objects.Measure;
import Objects.Sex;
import Objects.Unit;
import WS.Configuracion;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author armgarces
 */
public class BdConfig extends Bd{
    private String mensj;
    /***************************************************************************/
    /********************************** BD *************************************/
    /***************************************************************************/
    /**
     * 
     */
    public BdConfig(){
       
    }  
    /**
     * 
     * @param value
     * @return 
     */
    private String selectLoadSP(ConfigInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Sex":
                return "{call sp_get_sex_all}";
            case "Unit":
                return "{call sp_get_unit_all}";
            case "Measure":
                return "{call sp_get_measure_all}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectFindSP(ConfigInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Sex":
                params = new String[]{"@find"};
                return "{call sp_get_sex_find(?)}";
            case "Unit":
                params = new String[]{"@find"};
                return "{call sp_get_unit_find(?)}";
            case "Measure":
                params = new String[]{"@find"};
                return "{call sp_get_measure_find(?)}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectCheckSP(int value){
        switch(value){
            case Configuracion.CHECK_SEX:
                params = new String[]{"@id_sex"};
                return "{call sp_get_sex_check(?,?)}";
            case Configuracion.CHECK_UNIT:
                params = new String[]{"@unit"};
                return "{call sp_get_unit_check(?,?)}";
            case Configuracion.CHECK_MEASURE:
                params = new String[]{"@measure"};
                return "{call sp_get_measure_check(?,?)}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectChangeSP(ConfigInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Sex":
                mensj = "deleteSex:";
                params = new String[]{"@id_sex","@status"};
                return "{call sp_upd_sex_status(?,?)}";
            case "Unit":
                mensj = "deleteUnit:";
                params = new String[]{"@id_unit","@status"};
                return "{call sp_upd_unit_status(?,?)}";
            case "Measure":
                mensj = "deleteMeasure:";
                params = new String[]{"@id_measure","@status"};
                return "{call sp_upd_measure_status(?,?)}";
            default:
                return "";
        }
    }
    /***************************************************************************/
    /******************************** CONFIG ***********************************/
    /***************************************************************************/    
    /**
     * 
     * @param value
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public ConfigInterface[] load(ConfigInterface value) throws SQLException, BdException {        
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append(selectLoadSP(value));            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();                           
               List<ConfigInterface> list = new ArrayList<>();
                while(result.next()) {
                   ConfigInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new ConfigInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param value
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public ConfigInterface[] find(ConfigInterface value,String find) throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();                               
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(value); 
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                cstmt.setString(params[0], find);
                ResultSet result = cstmt.executeQuery();            
                List<ConfigInterface> list = new ArrayList<>();
                while(result.next()) {
                   ConfigInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new ConfigInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * Valida si la variable ya se encuentra asignada a una medida/sexo o unidad en la BD
     * @param check
     * @param name valor a ser chequeado en la BD
     * @return true si esta en uso, false si esta disponible
     * @throws java.sql.SQLException
     * @throws Interfaces.BdException
     */
    public boolean check(int check, String name) throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();                               
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectCheckSP(check)); 
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setString(params[0], name);
                cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
                cstmt.execute();
                return cstmt.getInt("result")==1;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param user
     * @param value
     * @return
     * @throws SQLException
     * @throws BdException 
     */
    public boolean change(String user,ConfigInterface value) throws SQLException, BdException {       
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sqlProc = new StringBuilder();                
                sqlProc.append(selectChangeSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
                cstmt.setInt(params[0], value.getId());
                int stat = 0;
                if(value.getStatus() == 0){
                    stat = 1;
                }            
                cstmt.setInt(params[1], stat );
                cstmt.execute();            
                // Auditar el proceso
                auditar(user, value.getId() + "",mensj);
                connection.commit();
                return true; 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }        
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param sex
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean saveSex(String user, int operacion, Sex sex) throws SQLException, BdException{        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_sex(?, ?)}");
                        mensj = "Nuevo Sexo:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_sex_basic(?, ?, ?)}");
                        mensj = "Actualizando Sexo:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@nombre"       , sex.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , sex.getAbrev().toUpperCase());
                        break;
                    case 2:
                        cstmt.setInt("@grp_supplier"    , sex.getIdSexo());
                        cstmt.setString("@nombre"       , sex.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , sex.getAbrev().toUpperCase());
                        break;
                }
                cstmt.execute();            
                // Auditar el proceso
                auditar(user, sex.getIdSexo()+ "", mensj);
                connection.commit();
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param unit
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean saveUnit(String user, int operacion, Unit unit) throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_unit(?, ?)}");
                        mensj = "Nueva Unidad:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_unit_basic(?, ?, ?)}");
                        mensj = "Actualizando Unidad:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@nombre"       , unit.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , unit.getAbrev().toUpperCase());
                        break;
                    case 2:
                        cstmt.setInt("@id_unit"         , unit.getIdUnit());
                        cstmt.setString("@nombre"       , unit.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , unit.getAbrev().toUpperCase());
                        break;
                }
                cstmt.execute();            
                // Auditar el proceso
                auditar(user, unit.getIdUnit()+ "", mensj);
                connection.commit();
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param measure
     * @return
     * @throws SQLException
     * @throws Interfaces.BdException
     */
    public boolean saveMeasure(String user, int operacion, Measure measure) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();  
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";            
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_measure(?, ?)}");
                        mensj = "Nueva Medida:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_measure_basic(?, ?, ?)}");
                        mensj = "Actualizando Medida:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@nombre" , measure.getNombre().toUpperCase());
                        cstmt.setString("@abrev"  , measure.getAbrev().toUpperCase());
                        break;
                    case 2:
                        cstmt.setInt("@id_measure", measure.getIdMeasure());
                        cstmt.setString("@nombre" , measure.getNombre().toUpperCase());
                        cstmt.setString("@abrev"  , measure.getAbrev().toUpperCase());
                        break;
                } 
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,measure.getIdMeasure() + "",mensj);        
                connection.commit();
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }
    /***************************************************************************/
    /******************************* SINGLETON *********************************/
    /***************************************************************************/    
    private static class BdHolder {
        private static final BdConfig INSTANCE = new BdConfig();
    }
    public static BdConfig getInstance() {
        return BdConfig.BdHolder.INSTANCE;
    }
}
